<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class InsertOrUpdateStreamData extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'stream:insert-or-update {date?}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Insert or update data in zb_finance_union_room_stream_days table based on the given date';

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        // 获取传递的日期参数，如果不传则使用当天日期
        $dateParam = $this->argument('date') ?: date('Y-m-d', strtotime('-1 day'));
//        $dateParam = '2025-01-23';
        // 日期范围
        $startDateTime = $dateParam . ' 00:00:00';
        $endDateTime = $dateParam . ' 23:59:59';

        $list = DB::table("union_room")->selectRaw('un_id as union_id,room_id')->get();

        foreach ($list as $item) {
            // 构造查询逻辑
            $query = DB::table('finance_union_room_stream')
                ->selectRaw('
                `union_id`, 
                `room_id`, 
                SUM(data_num * data_value) as total,
                ? as created_at', [$dateParam])
                ->where("union_id", $item->union_id)
                ->where("room_id", $item->room_id)
//            ->where("union_id", 10042)
//            ->where("room_id", 14698)
                ->whereBetween('created_at', [$startDateTime, $endDateTime])
                ->whereIn('data_type', [1, 2])
                ->groupBy('union_id', 'room_id');
            // 转换成插入数据
            $row = $query->first();

            if ($row) {
                $sql = "select * from zb_finance_union_room_stream_days where union_id =? and room_id =? and created_at=?  limit 1 ";
                $r = DB::select($sql, [$row->union_id, $row->room_id, $row->created_at]);
                if (!$r) {
                    // 插入逻辑
                    DB::table('finance_union_room_stream_days')->updateOrInsert(
                        [
                            'union_id' => $row->union_id,
                            'room_id' => $row->room_id,
                            'created_at' => $row->created_at,
                        ],
                        [
                            'total' => $row->total,
                        ]
                    );
                    echo "insert";
                } else {
                    // 更新逻辑
                    DB::table('finance_union_room_stream_days')
                        ->where('created_at', $row->created_at)
                        ->where('union_id', $row->union_id)
                        ->where('room_id', $row->room_id)
                        ->update([
                            'total' => $row->total,
                        ]);
                    echo "update";
                }
            }
        }
        $this->info('Insert or update operation completed successfully.');
        return 0;
    }
}